"""
Case Type   : Compatibility
Case Name   : 非数值类型导入
Create At   : 2023/07/19
Owner       : n@ningyali
Description :
        1.创建表，指定列类型为非数值类型
        2.插入数据
        3.使用copy导出数据，生成文件
        4.load data使用第3步生成文件导入数据
        5.查询表数据
        6.清理数据库，删除数据文件
Expect      :
        1.成功
        2.成功
        3.成功
        4.成功
        5.成功
        6.成功
History     :
    2024/9/9 @ningyali 16进制默认显示方式变更，回显变更，优化用例
"""
import os
import unittest

from yat.test import Node

from testcase.COMPATIBILITY.GRAMMAR.LOAD. \
    Opengauss_Compatibility_Grammar_Load_Common import LoadData
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class LoadData0091(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.constant = Constant()
        self.load_data = LoadData()
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.case_lable = 'load_data_0091'
        self.db_name = f'db_{self.case_lable}'
        self.t_name = f't_{self.case_lable}'
        self.type_name = f'type_{self.case_lable}'
        self.f_path = os.path.join('/home', self.pri_user.ssh_user,
                                   f'{self.case_lable}.txt')
        self.cols = '''col1 money, --货币类型
            col2 boolean, --布尔型
            col3 CHAR(4), --字符类型
            col4 CHARACTER(4), 
            col5 NCHAR(4),
            col6 VARCHAR(10),
            col7 CHARACTER VARYING(10),
            col8 VARCHAR2(10),
            col9 NVARCHAR2(10),
            col10 TEXT,
            col11 CLOB,
            col12 BLOB, --二进制类型
            col13 RAW,
            col14 BYTEA,
            col15 DATE, --时间日期类型
            col16 TIME,
            col17 time with time zone,
            col18 timestamp,
            col19 timestamp with time zone,
            col20 SMALLDATETIME,
            col21 abstime,
            col22 cidr, --网络地址类型
            col23 inet,
            col24 macaddr,
            col25 BIT(3), --位串类型
            col26 BIT VARYING(5),
            col27 UUID, --UUID类型
            col28 tsvector, --文本搜索类型
            col29 tsquery,
            col30 json, --json类型
            col31 jsonb,
            set hll(14), --HLL类型
            col33 int4range, --范围类型
            col34 int8range,
            col35 numrange,
            col36 tsrange,
            col37 tstzrange,
            col38 daterange,
            col39 HASH16, --账本数据库HASH类型
            col40 HASH32'''
        self.insert_sql = f"set bytea_output=escape;" \
            f"insert into {self.t_name} values (" \
            f"'52093.89'::money, " \
            f"true, 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', " \
            f"'aa', empty_blob(), HEXTORAW('DEADBEEF'), " \
            f"E'jo\\\\\\000se'::bytea, " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00 pst', " \
            f"'1900-01-01 00:00:00', '1900-01-01 00:00:00', " \
            f"'1910-01-01 00:00:00', '10.10.10.10', '10.10.10.10', " \
            f"'08002b010203', B'10'::bit(3), B'00', " \
            f"'a0eebc999c0b4ef8bb6d6bb9bd380a11', 'test', 'test', " \
            f"'{{{{\\\"aa\\\":1}}}}'::json, '{{{{\\\"bb\\\":2}}}}'::jsonb, " \
            f"hll_empty(14,-1), '[1,10]', '[1,10]', '[0.00, 10.00]', " \
            f"'[1900-01-01 00:00:00, 2020-01-01 00:00:00]', " \
            f"'[1900-01-01 00:00:00 pst, 2020-01-01 00:00:00 pst]', " \
            f"'(1900-01-01, 2020-01-01)','ffff', " \
            f"'ffffffffffffffffffffffffffffffff');"

        text = '-----prestep1:创建兼容B库；expect:创建成功-----'
        self.log.info(text)
        res = self.load_data.create_b_db(self.pri_sh, self.db_name)
        self.assertTrue(res, '执行失败:' + text)

        text = '----prestep2:配置enable_copy_server_files参数值为on，' \
               '允许使用copy to/from expect: 成功----'
        self.log.info(text)
        befor_val = self.pri_sh.execute_gsguc('check', '',
                                              'enable_copy_server_files',
                                              get_detail=True)
        self.log.info(befor_val)
        guc_res = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'enable_copy_server_files=on')
        self.assertTrue(guc_res)

    def test_load(self):
        text = '-----step1：创建表，指定列类型为非数值类型；expect：建表成功-----'
        self.log.info(text)
        res = self.load_data.create_table(self.pri_sh, self.db_name,
                                          self.t_name, self.cols)
        self.assertTrue(res, '执行失败:' + text)

        text = '-----step2：插入数据；expect：成功-----'
        self.log.info(text)
        res = self.pri_sh.execut_db_sql(self.insert_sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('INSERT 0 1', res, '执行失败:' + text)

        text = '-----step3：使用copy导出数据；expect：成功-----'
        self.log.info(text)
        sql = f"copy (select * from t_load_data_0091) to '{self.f_path}';"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('COPY 1', res, '执行失败:' + text)

        text = '-----step4：导入数据；expect：成功-----'
        self.log.info(text)
        sql = f"truncate table {self.t_name};" \
            f"load data infile '{self.f_path}' into table {self.t_name};"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        self.log.info(res)
        self.assertIn('COPY 1', res, '执行失败' + text)

        text = '-----step5：查询表数据；expect：成功-----'
        self.log.info(text)
        sql = f"set bytea_output=escape;" \
              f"select * from {self.t_name};"
        res = self.pri_sh.execut_db_sql(sql, dbname=self.db_name)
        rows, _ = self.common.parse_sql_query_res(res)
        expect = [
            ['$52,093.89', 't', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa', 'aa',
             'aa', 'aa', '', 'DEADBEEF', 'jo\\000se', '1900-01-01',
             '00:00:00', '00:00:00+08:05:43', '1900-01-01 00:00:00+08:05:43',
             '1900-01-01 00:00:00+08:05:43', '1900-01-01 00:00:00',
             '1910-01-01 00:00:00+08', '10.10.10.10/32', '10.10.10.10',
             '08:00:2b:01:02:03', '010', '00',
             'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', "'test'", "'test'",
             '{"aa":1}', '{"bb": 2}',
             'HLL\\000\\000\\000\\000\\000+\\005\\000\\000\\000\\000\\000'
             '\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000\\000'
             '\\000\\000',
             '[1,11)', '[1,11)', '[0.00,10.00]',
             '["1900-01-01 00:00:00","2020-01-01 00:00:00"]',
             '["1900-01-01 00:00:00+08:05:43","2020-01-01 00:00:00+08"]',
             '[1900-01-02,2020-01-01)', '000000000000ffff',
             'ffffffffffffffffffffffffffffffff']]
        self.assertEqual(rows, expect, '执行失败' + text)

    def tearDown(self):
        s6_text = '----step6: 还原enable_copy_server_files参数值为off ' \
                  'expect: 成功----'
        self.log.info(s6_text)
        guc_res = self.pri_sh.execute_gsguc('reload',
                                            self.constant.GSGUC_SUCCESS_MSG,
                                            'enable_copy_server_files=off')
        after_val = self.pri_sh.execute_gsguc('check', '',
                                              'enable_copy_server_files',
                                              get_detail=True)
        self.log.info(after_val)

        self.log.info('-----step7：清理环境；expect：成功-----')
        res = self.load_data.clear_env(self.pri_sh, self.pri_user,
                                       self.db_name,
                                       self.f_path)
        self.assertTrue(res, '执行失败:清理环境')
        self.assertTrue(guc_res, f'执行失败: {s6_text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
